package Database;

import Entity.Account;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author CONG
 */
public class AccountDB {

    private ConnectDB conn;

    public AccountDB() {
        conn = new ConnectDB();
    }

    public ArrayList<Account> getAccountList() {
        ArrayList<Account> listAcc = new ArrayList<Account>();
        try {
            CallableStatement cstmt = conn.connect().prepareCall("{call getAccountList}");
            ResultSet rs = cstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("RegisterNo");
                Date registerDate = rs.getDate("RegisterDate");
                String name = rs.getString("Name");
                String phone = rs.getString("Phone");
                String address = rs.getString("Address");
                String email = rs.getString("Email");
                String org = rs.getString("Organization");
                float salary = rs.getFloat("Gross Salary");
                Account acc = new Account(id, registerDate, name, phone, address, email, org, salary);
                listAcc.add(acc);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AccountDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listAcc;
    }

    public Account addAccount(Account acc,int accountLoanCode,int period,int branchCode) {
        try {
            CallableStatement cstmt = conn.connect().prepareCall("{call insertCustomer(?,?,?,?,?,?,?,?,?,?,?)}");
            cstmt.registerOutParameter("registerNo", Types.INTEGER);
            Date today = new Date();
            java.sql.Date date = new java.sql.Date(today.getTime());
            cstmt.setInt("accountLoanCode",accountLoanCode);
            cstmt.setDate("registerDate", date);
            cstmt.setString("name", acc.getName());
            cstmt.setString("phone", acc.getPhone());
            cstmt.setString("address", acc.getAddress());
            cstmt.setString("email", acc.getEmail());
            cstmt.setString("organization", acc.getOrganization());
            cstmt.setFloat("salary", acc.getSalary());
            cstmt.setInt("branchCode", branchCode);
            cstmt.setInt("period", period);
            cstmt.executeUpdate();
            int registerNo = cstmt.getInt("RegisterNo");
            acc.setResgisterNo(registerNo);
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return acc;
    }

    public ArrayList<Account> searchCustomerWithAccount(int AccountCode) {
        ArrayList<Account> listAcc = new ArrayList<Account>();
        try {
            CallableStatement cstmt = conn.connect().prepareCall("{call searchCustomerWithAccount(?)}");
            cstmt.setInt("RegisterNo", AccountCode);
            ResultSet rs = cstmt.executeQuery();
//            if(!rs.next()){
//                JOptionPane.showConfirmDialog(null, "Nothing Found","Error",JOptionPane.ERROR_MESSAGE);
//            }
                while (rs.next()) {
                    int id = rs.getInt("RegisterNo");
                    Date registerDate = rs.getDate("RegisterDate");
                    String name = rs.getString("Name");
                    String phone = rs.getString("Phone");
                    String address = rs.getString("Address");
                    String email = rs.getString("Email");
                    String org = rs.getString("Organization");
                    float salary = rs.getFloat("Gross Salary");
                    Account acc = new Account(id, registerDate, name, phone, address, email, org, salary);
                    listAcc.add(acc);
                }
        } catch (Exception ex) {
            Logger.getLogger(ConnectDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listAcc;
    }
    
     public ArrayList<Account> searchCustomerWithLoanAmount(int LoanAmount) {
        ArrayList<Account> listAcc = new ArrayList<Account>();
        try {
            CallableStatement cstmt = conn.connect().prepareCall("{call searchCustomerWithLoanAmount(?)}");
            cstmt.setInt("loanAmount", LoanAmount);
            ResultSet rs = cstmt.executeQuery();
                while (rs.next()) {
                    int id = rs.getInt("RegisterNo");
                    Date registerDate = rs.getDate("RegisterDate");
                    String name = rs.getString("Name");
                    String phone = rs.getString("Phone");
                    String address = rs.getString("Address");
                    String email = rs.getString("Email");
                    String org = rs.getString("Organization");
                    float salary = rs.getFloat("Gross Salary");
                    Account acc = new Account(id, registerDate, name, phone, address, email, org, salary);
                    listAcc.add(acc);
                }
        } catch (Exception ex) {
            Logger.getLogger(ConnectDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listAcc;
    }
     
     
    public ArrayList<Account> searchCustomerWithPeriod(int period) {
        ArrayList<Account> listAcc = new ArrayList<Account>();
        try {
            CallableStatement cstmt = conn.connect().prepareCall("{call searchCustomerWithPeriod(?)}");
            cstmt.setInt("period", period);
            ResultSet rs = cstmt.executeQuery();
                while (rs.next()) {
                    int id = rs.getInt("RegisterNo");
                    Date registerDate = rs.getDate("RegisterDate");
                    String name = rs.getString("Name");
                    String phone = rs.getString("Phone");
                    String address = rs.getString("Address");
                    String email = rs.getString("Email");
                    String org = rs.getString("Organization");
                    float salary = rs.getFloat("Gross Salary");
                    Account acc = new Account(id, registerDate, name, phone, address, email, org, salary);
                    listAcc.add(acc);
                }
        } catch (Exception ex) {
            Logger.getLogger(ConnectDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listAcc;
    }
    
    public boolean checkEmail(String email){
        ResultSet rs = null;
        try {
            CallableStatement cstmt=conn.connect().prepareCall("{call checkEmailExist(?)}");
            cstmt.setString("email", email);
            rs=cstmt.executeQuery();
            return rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(ConnectDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    public void deleteDuration(int registerNo){
        try {
            CallableStatement cstmt=conn.connect().prepareCall("{call deleteDuration(?)}");
            cstmt.setInt("registerNo", registerNo);
            cstmt.executeUpdate();
        } catch (Exception ex) {
            Logger.getLogger(ConnectDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
